﻿using Dapper;
using FRMS.Helper;
using FRMS.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace FRMS.DAL
{
    public class UserDAL
    {
        public bool AddUser(User userInfo)
        {
            string strSql = string.Format("INSERT INTO user_info(LoginName,LoginPwd,name,projectID)VALUES('{0}','{1}','{2}',{3})", userInfo.LoginName, userInfo.LoginPwd, userInfo.name, userInfo.projectID);
            return SqlHelper<User>.SqlExecute(strSql);
        }

        public bool UpdateUser(User userInfo)
        {
            string strSql = "UPDATE user_info SET LoginName='" + userInfo.LoginName + "',LoginPwd='" + userInfo.LoginPwd + "',name='" + userInfo.name + "',foption='" + userInfo.foption + "' WHERE ID=" + userInfo.ID;

            return SqlHelper<User>.SqlExecute(strSql);
        }

        public bool DelUser(User userInfo)
        {
            string strSql = "UPDATE user_info SET IsDelete=1 WHERE ID=" + userInfo.ID;
            return SqlHelper<User>.SqlExecute(strSql);
        }

        public bool ModifyPwd(User userInfo)
        {
            string strSql;
            if (!string.IsNullOrEmpty(userInfo.LoginPwd))
            {
                strSql = "UPDATE user_info SET LoginPwd='" + userInfo.LoginPwd + "' WHERE ID=" + userInfo.ID;
            }
            else
            {
                strSql = "UPDATE user_info SET LoginPwd='123456' WHERE ID=" + userInfo.ID;
            }
            return SqlHelper<User>.SqlExecute(strSql);
        }

        public User LoadUser(int id)
        {
            string strSql = "SELECT * FROM user_info WHERE ID=" + id;
            return SqlHelper<User>.SqlSingleData(strSql);
        }

        public List<User> QueryUserList(User userInfo)
        {
            string strSql = "SELECT * FROM user_info";
            string sqlwhere = string.Empty;

            if (userInfo.projectID > 0)
            {
                sqlwhere += " projectID=" + userInfo.projectID + " AND";
            }
            if (!string.IsNullOrEmpty(userInfo.LoginName))
            {
                sqlwhere += " LoginName like '%" + userInfo.LoginName + "%' AND";
            }
            if (!string.IsNullOrEmpty(userInfo.name))
            {
                sqlwhere += " name like '%" + userInfo.name + "%' AND";
            }
            sqlwhere += " IsDelete=0 AND";

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            if (userInfo.Recordhead > -1 && userInfo.Recordtail > 0)
            {
                strSql += " LIMIT " + userInfo.Recordhead + "," + userInfo.Recordtail;
            }

            return SqlHelper<User>.SqlList(strSql);
        }

        public int QueryUserListCount(User userInfo)
        {
            string strSql = "SELECT COUNT(1) FROM user_info";
            string sqlwhere = string.Empty;

            if (userInfo.projectID > 0)
            {
                sqlwhere += " projectID=" + userInfo.projectID + " AND";
            }
            if (!string.IsNullOrEmpty(userInfo.LoginName))
            {
                sqlwhere += " LoginName like '%" + userInfo.LoginName + "%' AND";
            }
            if (!string.IsNullOrEmpty(userInfo.name))
            {
                sqlwhere += " name like '%" + userInfo.name + "%' AND";
            }
            sqlwhere += " IsDelete=0 AND";

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            if (userInfo.Recordhead > -1 && userInfo.Recordtail > 0)
            {
                strSql += " LIMIT " + userInfo.Recordhead + "," + userInfo.Recordtail;
            }

            return SqlHelper<User>.SqlScalar(strSql);
        }

        public int CheckUser(string name)
        {
            string strSql = "SELECT COUNT(1) FROM user_info where LoginName='" + name + "' AND IsDelete=0";

            return SqlHelper<User>.SqlScalar(strSql);
        }

        public List<Menu> GetMenu(int proid)
        {
            string strsql = "SELECT * FROM menu_info WHERE FIND_IN_SET(ID ,(SELECT MenuRole FROM project_info WHERE ID=" + proid + ")) AND IsDelete=0";
            return SqlHelper<Menu>.SqlList(strsql);
        }

        public List<Menu> GetParentMenu(int proid)
        {
            string strsql = "SELECT * FROM menu_info WHERE ID in(SELECT DISTINCT ParentID FROM menu_info WHERE FIND_IN_SET(ID ,(SELECT MenuRole FROM project_info WHERE ID=" + proid + ")) AND IsDelete=0)";
            return SqlHelper<Menu>.SqlList(strsql);
        }

        public List<FOption> GetFOptions(int menuid)
        {
            string strsql = "SELECT * FROM foption WHERE MenuID=" + menuid + " AND IsDelete=0";
            return SqlHelper<FOption>.SqlList(strsql);
        }
    }
}
